/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package anton.dinner;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.bean.SessionScoped;
import javax.sql.DataSource;

/**
 *
 * @author johan
 */
@ManagedBean(name="listDinner")
@SessionScoped

public class ListDinner implements Serializable {
    
    @Resource(name="jdbc/antonsDatasource") // data source to use for mysql queries
    
	private DataSource ds;
    
    @ManagedProperty(value="#{listSpecIngredients}")
    
    private ListSpecIngredients listspec;

    public ListSpecIngredients getListspec() {
        return listspec;
    }

    public void setListspec(ListSpecIngredients listspec) {
        this.listspec = listspec;
    }
    
    private DinnerEntity dinnerEntity;// holds a dinner type
    private DinnerEntity dinner;
    
    // variables used  to manage specific selected dinner type 
    private String dinnerName; 
    private String dinnerDescription;
    private int dinnerPrice;
    private String dinnerType;
    private int itemId;
    private String selectedValue;    
    private int i;
    
    private List<String> selectedIngredients;
    

    public List<String> getSelectedIngredients() {
        return selectedIngredients;
    }

    public void setSelectedIngredients(List<String> selectedIngredients) {
        this.selectedIngredients = selectedIngredients;
    }
    
        List<DinnerEntity> list; // list for dinner entities used to produce output
    
        
    //--------------------------------------------------------------------------
    // Setter and getters

    /**
     * @return the dinnerName
     */
    public int getItemId() {
        return itemId;
    }

    public void setItemId(int itemId) {
        this.itemId = itemId;
    }
    
    
    
    public String getDinnerName() {
        return dinnerName;
    }

    /**
     * @param dinnerName the dinnerName to set
     */
    public void setDinnerName(String dinnerName) {
        this.dinnerName = dinnerName;
    }

    /**
     * @return the dinnerDescription
     */
    public String getDinnerDescription() {
        return dinnerDescription;
    }

    /**
     * @param dinnerDescription the dinnerDescription to set
     */
    public void setDinnerDescription(String dinnerDescription) {
        this.dinnerDescription = dinnerDescription;
    }

    /**
     * @return the dinnerPrice
     */
    public int getDinnerPrice() {
        return dinnerPrice;
    }

    /**
     * @param dinnerPrice the dinnerPrice to set
     */
    public void setDinnerPrice(int dinnerPrice) {
        this.dinnerPrice = dinnerPrice;
    }

    /**
     * @return the dinnerType
     */
    public String getDinnerType() {
        return dinnerType;
    }

    /**
     * @param dinnerType the dinnerType to set
     */
    public void setDinnerType(String dinnerType) {
        this.dinnerType = dinnerType;
    }
      
        
   // used to get selected value in list
    public String getSelectedValue() {
        return selectedValue;
    }
    // used to set selected value
    public void setSelectedValue(String selectedValue) {
        this.selectedValue = selectedValue;
    }
    
    //--------------------------------------------------------------------------
    
   // method used to fill array list with dinner entities   
   public List<DinnerEntity> getDinnerIngredients() throws SQLException{
                    
       
		if(ds==null)
			throw new SQLException("Can't get data source");// throws exception i database not available
        try (Connection con = ds.getConnection()) {
            if(con==null)
                throw new SQLException("Can't get database connection");
            PreparedStatement ps
                    = con.prepareStatement(
                            "Select dinner_name,dinner_description, dinner_price, dinner_type  from Dinner"); // query select from dinner specified attributes
            
            //get  item info from database
            ResultSet result =  ps.executeQuery();
            
            list = new ArrayList<>();
            
            
            while(result.next()){
                
                dinner=new DinnerEntity();
               
                dinner.setDinnerName(result.getString("dinner_name"));
                dinner.setDinnerDescription(result.getString("dinner_description"));
                dinner.setDinnerPrice(result.getInt("dinner_price"));
                dinner.setDinnerType(result.getString("dinner_type"));
                
                
                //adds ingredients to list
                list.add(dinner);
            }
        }
		return list;
                
	}// ends method
   
   
   // method used to delete an entrie only possible to delete if no orders exist
   //
   public String destroy(String DinnerName) throws SQLException
   {    
        String sql;// variable for sql statements
        String target = "/admin/listdinner?faces-redirect=true";
        
        try (Connection con = ds.getConnection()) {
            sql="delete from DinnerIngredients where dinner_name ="+"'"+DinnerName+"'";
            
            
            PreparedStatement ps; // query delete items
            ps = con.prepareStatement(sql );
            
            i= ps.executeUpdate();
            sql="delete from Dinner where dinner_name ="+"'"+DinnerName+"'";
            
            ps = con.prepareStatement(sql );
            
            i= ps.executeUpdate();
            
            // removes deleted items from list
            for(int i=0; i<list.size(); i++)
                
                
            {
                dinnerEntity= list.get(i);
                
                if(DinnerName.equals(dinnerEntity.getDinnerName()))
                    list.remove(i);
                
            }
        }
        return target;
   
   }
    
    // produces output concerning one dinner
    public String niceView(String dinnername) throws SQLException
            
    {  
        String sql;
        
        String target = "/admin/dinner/view?faces-redirect=true";
        
        // select one matching dinner, dinner_name primary key in mysql table
        sql="Select dinner_name,dinner_description, dinner_price, dinner_type  from Dinner where dinner_name="+"'"+dinnername+"'";
        
        try (Connection con = ds.getConnection()) 
        {
            PreparedStatement ps; 
            ps = con.prepareStatement(sql );
            
            ResultSet result =  ps.executeQuery();
            
            while(result.next())
            { 
                dinnerName = result.getString("dinner_name");
                dinnerDescription = result.getString("dinner_description");
                dinnerPrice =(result.getInt("dinner_price"));
                dinnerType =(result.getString("dinner_type"));
            }
        }
        return target;
    }
    
 // method that prepares a dinner course to be edited   "gets the current row"
 public String prepareUpdate(String dinnerName,String dinnerDescription,int dinnerPrice, String dinnerType) throws SQLException
            
            
   {   String target="/admin/dinner/updatedinner?faces-redirect=true";
   
       listspec = new ListSpecIngredients();
       listspec.setDatasource(ds);
       selectedIngredients = listspec.getSelectedIngredients(dinnerName);
       this.selectedValue=dinnerName;
       this.dinnerName= dinnerName;
       this.dinnerDescription= dinnerDescription;
       this.dinnerPrice = dinnerPrice;
       this.dinnerType= dinnerType;  
       
       return target;
       
   }
    
   
  
    public  String upDate( String dinnername) throws SQLException // Method used to update dinner
    {  
        
        ArrayList<Integer> templist= new ArrayList<>();// temporary list used to hold integer values from table DinnerIngredients
         
        if(ds==null)
			throw new SQLException("Can't get data source");// throws exception if database not available
        String target;
        
        try (Connection con = ds.getConnection()) {
            
            if(con==null)
                throw new SQLException("Can't get database connection");
            
            String sql;
            target = "/admin/updatedinner?faces-redirect=true";
            
            
            sql= " delete from DinnerIngredients where dinner_name='"+dinnername+"'";// querie used to delete all entries containing the specefic dinnername later updated from select list.
                
                try (PreparedStatement ps = con.prepareStatement(
                        sql ))
                {
                    i= ps.executeUpdate();
                    
                }
            
            
         
                sql="UPDATE Dinner set dinner_name = ?, dinner_description=?, dinner_price=?, dinner_type=?  where dinner_name ='"+dinnername+"'" ;
                try (PreparedStatement ps = con.prepareStatement(
                        sql )){
                    
                    //insert into Dinner(dinner_name,dinner_description,dinner_price,dinner_type ) values (?,?,?,?)
                    //UPDATE Dinner set dinner_name = 'Ärtsoppa', dinner_description='gula ärtor och rimmat sidfläsk', dinner_price=90, dinner_type='starter'  where dinner_name = 'Ärtsoppa'; 
                    
                    ps.setString( 1, dinnerName );
                    ps.setString(2, dinnerDescription);
                    ps.setInt(3, dinnerPrice);
                    ps.setString(4, dinnerType);
                    
                    
                    i= ps.executeUpdate();
                    
                }
                
                sql="insert into DinnerIngredients (dinner_name,item_ID ) values (?,?)";
                
                try (PreparedStatement ps = con.prepareStatement(sql)) {
                    
                    
                    
                    
                    for(int j=0;j < selectedIngredients.size();j++)
                    {
                     String tempString= selectedIngredients.get(j);
                     
                     int tempInt= Integer.parseInt(tempString);
                    
                     templist.add(tempInt);
                    
                    
                    }
                    
                    
                    for(int k=0;k < templist.size();k++)
                    {
                        
                        ps.setString( 1, dinnerName );
                        
                        ps.setInt(2, templist.get(k));
                        
                        ps.executeUpdate();
                        
                    }
                    
                }
                
            }
        return target;
    
     }
    
    
    
    
    
    
}
